﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;
using System.Collections;


namespace DAO
{
    public class ChiTietPhieuNhapDAO
    {
        public static int ThemChiTietPhieuNhap(ChiTietPhieuNhapDTO dv)
        {
            string sql;
            sql = "Insert into ChiTietPhieuNhap(MaChiTietPhieuNhap,MaPhieuNhap,MaSach,SoLuongNhap) values ('" + dv.MaChiTietPhieuNhap + "','" + dv.PhieuNhap.MaPhieuNhap + "','" + dv.Sach.MaSach + "'," + dv.SoLuongNhap + ")";
            int kq = DataAccess.ExcuNonQuery(sql);
            return kq;
        }

        public static ChiTietPhieuNhapDTO LayThongTinCTPhieuNhap(String ma)
        {
            ChiTietPhieuNhapDTO ctpn = new ChiTietPhieuNhapDTO();
            string sql = "Select * From ChiTietPhieuNhap where MaChiTietPhieuNhap = '" + ma + "'";
            DataTable dt = DataAccess.ExcuQuery(sql);

            if (dt != null && dt.Rows.Count > 0)
            {
                ctpn.MaChiTietPhieuNhap = Convert.ToString(dt.Rows[0]["MaChiTietPhieuNhap"]);
                ctpn.PhieuNhap = PhieuNhapDAO.LayThongTinPhieuNhap(dt.Rows[0]["MaPhieuNhap"].ToString());
                ctpn.Sach = SachDAO.LayThongTinSach(dt.Rows[0]["MaSach"].ToString());
                ctpn.SoLuongNhap = Int32.Parse(dt.Rows[0]["SoLuongNhap"].ToString());
                ctpn.TrangThai = Convert.ToInt32(dt.Rows[0]["TrangThai"]);
            }
            return ctpn;
        }

        public static int CapNhat(ChiTietPhieuNhapDTO ctpn)
        {
            string sql;
            sql = "Update ChiTietPhieuNhap Set MaPhieuNhap = '" + ctpn.PhieuNhap.MaPhieuNhap + "', MaSach='" + ctpn.Sach.MaSach + "', SoLuongNhap=" + ctpn.SoLuongNhap + ", TrangThai= " + ctpn.TrangThai + " where MaChiTietPhieuNhap = '" + ctpn.MaChiTietPhieuNhap + "'";
            int kq = DataAccess.ExcuNonQuery(sql);
            return kq;
        }

        public static int XoaLuon(String ma)
        {
            string sql;
            sql = "DELETE FROM ChiTietPhieuNhap where MaChiTietPhieuNhap = '" + ma + "'";
            int kq = DataAccess.ExcuNonQuery(sql);
            return kq;
        }

        public static int Xoa(String ma)
        {
            string sql;
            sql = "Update ChiTietPhieuNhap Set TrangThai= 0 where MaChiTietPhieuNhap = '" + ma + "'";
            int kq = DataAccess.ExcuNonQuery(sql);
            return kq;
        }

        public static ArrayList LayDanhSachTheoPhieuNhap(String mapn)
        {
            ArrayList arr = new ArrayList();
            string sql;
            sql = "Select * From ChiTietPhieuNhap where TrangThai=1 and MaPhieuNhap='" + mapn + "'";
            DataTable dt = DataAccess.ExcuQuery(sql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                ChiTietPhieuNhapDTO dv = new ChiTietPhieuNhapDTO();
                dv.MaChiTietPhieuNhap = Convert.ToString(dt.Rows[i]["MaChiTietPhieuNhap"]);
                dv.PhieuNhap = PhieuNhapDAO.LayThongTinPhieuNhap(dt.Rows[i]["MaPhieuNhap"].ToString());
                dv.Sach = SachDAO.LayThongTinSach(dt.Rows[i]["MaSach"].ToString());
                dv.SoLuongNhap = Int32.Parse(dt.Rows[i]["SoLuongNhap"].ToString());
                dv.TrangThai = Convert.ToInt32(dt.Rows[i]["TrangThai"]);
                arr.Add(dv);
            }
            return arr;
        }

        public static ArrayList LayDanhSachTheoSachVaThoiGian(String masach, DateTime thoigian)
        {
            ArrayList arr = new ArrayList();
            string sql;
            sql = "select * from ChiTietPhieuNhap ctpn,PHIEUNHAP pn" +
                    " where MONTH(pn.NGAYNHAP)=" + thoigian.Month + " and YEAR(pn.NGAYNHAP)=" + thoigian.Year + " and ctpn.MaSach='" + masach + "' AND ctpn.TrangThai=1" +
                    " and ctpn.MaPhieuNhap=pn.MaPhieuNhap";
            DataTable dt = DataAccess.ExcuQuery(sql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                ChiTietPhieuNhapDTO dv = new ChiTietPhieuNhapDTO();
                dv.MaChiTietPhieuNhap = Convert.ToString(dt.Rows[i]["MaChiTietPhieuNhap"]);
                dv.PhieuNhap = PhieuNhapDAO.LayThongTinPhieuNhap(dt.Rows[i]["MaPhieuNhap"].ToString());
                dv.Sach = SachDAO.LayThongTinSach(dt.Rows[i]["MaSach"].ToString());
                dv.SoLuongNhap = Int32.Parse(dt.Rows[i]["SoLuongNhap"].ToString());
                dv.TrangThai = Convert.ToInt32(dt.Rows[i]["TrangThai"]);
                arr.Add(dv);
            }
            return arr;
        }
    }
}
